# coding=utf-8
import datetime
import sys
import urllib

import pymysql
from lxml import etree

reload(sys)
sys.setdefaultencoding('utf8')

# db = pymysql.Connect(
#     host='127.0.0.1',
#     port=3306,
#     user='root',
#     passwd='',
#     db='gupiao',
#     charset='utf8'
# )

db = pymysql.Connect(
    host='apiccr.megowx.com',
    port=3306,
    user='gupiao',
    passwd='Aitrip123',
    db='gupiao',
    charset='utf8'
)
cursor = db.cursor()

# 季报出后可以运行一次 3个月

sql_dk = "INSERT INTO `fa_stock_dfc` (`code`, `name`, `zy1`, `zy2`, `zy3`, `zy4`, `zy5`, `zy6`, " \
         "`tb1`, `tb2`, `tb3`, `tb4`, `tb5`, `gd1_1`, `gd1_2`, `gd1_3`, `gd1_4`, `gd1_5`, `gd2_1`, `gd2_2`, " \
         "`gd2_3`, `gd2_4`, `gd2_5`, `gd3_1`, `gd3_2`, `gd3_3`, `gd3_4`, `gd3_5`, `fz1`, `fz2`, `fz3`, `fz4`, " \
         "`fz5`, `fz6`, `xj1_1`, `xj1_2`, `xj1_3`, `xj1_4`, `xj1_5`, `xj1_6`, `xj2_1`, `xj2_2`, `xj2_3`, " \
         "`xj2_4`, `xj2_5`, `xj2_6`, `xj3_1`, `xj3_2`, `xj3_3`, `xj3_4`, `xj3_5`, `xj3_6`, `gb1`, `gb2`, " \
         "`lr1_1`, `lr1_2`, `lr1_3`, `lr1_4`, `lr1_5`, `lr1_6`, `lr2_1`, `lr2_2`, `lr2_3`, `lr2_4`, `lr2_5`, " \
         "`lr2_6`, `lr3_1`, `lr3_2`, `lr3_3`, `lr3_4`, `lr3_5`, `lr3_6`, `lr4_1`, `lr4_2`, `lr4_3`, `lr4_4`, " \
         "`lr4_5`, `lr4_6`, `lr5_1`, `lr5_2`, `lr5_3`, `lr5_4`, `lr5_5`, `lr5_6`, `lr6_1`, `lr6_2`, `lr6_3`, " \
         "`lr6_4`, `lr6_5`, `lr6_6`, `lr7_1`, `lr7_2`, `lr7_3`, `lr7_4`, `lr7_5`, `lr7_6`, `lr8_1`, `lr8_2`, " \
         "`zf1_1`, `zf1_2`, `zf1_3`, `zf1_4`, `zf1_5`, `zf1_6`, `zf2_1`, `zf2_2`, `zf2_3`, `zf2_4`, `zf2_5`, " \
         "`zf2_6`, `zf3_1`, `zf3_2`, `zf3_3`, `zf3_4`, `zf3_5`, `zf3_6`, `fh1_1`, `fh1_2`, `fh1_3`, `fh1_4`," \
         " `fh2_1`, `fh2_2`, `fh2_3`, `fh2_4`, `fh3_1`, `fh3_2`, `fh3_3`, `fh3_4`, `fh4_1`, `fh4_2`, `fh4_3`," \
         " `fh4_4`, `fh5_1`, `fh5_2`, `fh5_3`, `fh5_4`) VALUES  "


def getHtml(url):
    html = urllib.urlopen(url).read()
    html = html.decode('gbk')
    return html


def getHtml2(url):
    html = urllib.urlopen(url).read()
    html = html.decode('utf-8')
    return html


def querydata(code):
    global sql_dk
    global cursor
    global db

    code0 = code[3:]

    a0 = []
    sql = sql_dk
    thisyear = int(datetime.datetime.now().strftime('%Y'))

    str1 = "'" + code + "'"
    name = ""
    code = code.replace(".", "").upper()
    # 股权质押
    zy1 = zy2 = zy3 = zy4 = zy5 = zy6 = "0"
    # try:
    #     Url = "http://f10.eastmoney.com/CompanyBigNews/Index?type=web&code=" + code + "#"
    #     print(Url)
    #     html = getHtml2(Url)
    #     data = etree.HTML(html)
    #
    #     name = data.xpath('//*[@id="hq_1"]')[0].text
    #     # 质押股数(万股)
    #     zy1 = data.xpath('//*[@id="gqzy_Table"]/tbody/tr[5]/td[1]')[0].text.replace(",", "")
    #     # 占总股本比例( %)
    #     zy2 = data.xpath('//*[@id="gqzy_Table"]/tbody/tr[6]/td[1]')[0].text.replace(",", "")
    #     # 占股东持股比例( %)
    #     zy3 = data.xpath('//*[@id="gqzy_Table"]/tbody/tr[7]/td[1]')[0].text.replace(",", "")
    #     # 质押起始日
    #     zy4 = data.xpath('//*[@id="gqzy_Table"]/tbody/tr[8]/td[1]')[0].text.replace(",", "")
    #     # 质押截止日
    #     zy5 = data.xpath('//*[@id="gqzy_Table"]/tbody/tr[9]/td[1]')[0].text.replace(",", "")
    #     # 解押日期
    #     zy6 = data.xpath('//*[@id="gqzy_Table"]/tbody/tr[10]/td[1]')[0].text.replace(",", "")
    #
    #
    # except Exception as e:
    #     print(e)

    str1 += ",'" + name + "'"
    str1 += ",'" + str(zy1) + "'"
    str1 += ",'" + str(zy2) + "'"
    str1 += ",'" + str(zy3) + "'"
    str1 += ",'" + str(zy4) + "'"
    str1 += ",'" + str(zy5) + "'"
    str1 += ",'" + str(zy6) + "'"

    # 同行对比
    tb1 = tb2 = tb3 = tb4 = tb5 = "0"
    # try:
    #     Url = "http://f10.eastmoney.com/IndustryAnalysis/Index?type=web&code=" + code + "#"
    #     html = getHtml(Url)
    #     data = etree.HTML(html)
    #
    #     # 公司规模排名
    #     tb1 = data.xpath('//*[@id="tbzsz"]/tr[1]/td[1]')[0].text.replace(",", "")
    #     # 总市值(元)
    #     tb2 = data.xpath('//*[@id="tbzsz"]/tr[1]/td[4]')[0].text.replace(",", "")
    #     # 流通市值(元)
    #     tb3 = data.xpath('//*[@id="tbzsz"]/tr[1]/td[5]')[0].text.replace(",", "")
    #     # 营业收入(元)
    #     tb4 = data.xpath('//*[@id="tbzsz"]/tr[1]/td[6]')[0].text.replace(",", "")
    #     # 净利润(元)
    #     tb5 = data.xpath('//*[@id="tbzsz"]/tr[1]/td[7]')[0].text.replace(",", "")
    # except Exception as e:
    #     print(e)

    str1 += ",'" + str(tb1) + "'"
    str1 += ",'" + str(tb2) + "'"
    str1 += ",'" + str(tb3) + "'"
    str1 += ",'" + str(tb4) + "'"
    str1 += ",'" + str(tb5) + "'"

    # 股东人数
    gd1_1 = gd1_2 = gd1_3 = gd1_4 = gd1_5 = "0"
    gd2_1 = gd2_2 = gd2_3 = gd2_4 = gd2_5 = "0"
    gd3_1 = gd3_2 = gd3_3 = gd3_4 = gd3_5 = "0"
    # try:
    #     Url = "http://f10.eastmoney.com/ShareholderResearch/Index?type=web&code=" + code + "#"
    #     html = getHtml(Url)
    #     data = etree.HTML(html)
    #
    #     # 股东人数(户)
    #     gd1_1 = data.xpath('//*[@id="Table0"]/tbody/tr[3]/td[1]')[0].text.replace(",", "")
    #     gd1_2 = data.xpath('//*[@id="Table0"]/tbody/tr[3]/td[2]')[0].text.replace(",", "")
    #     gd1_3 = data.xpath('//*[@id="Table0"]/tbody/tr[3]/td[3]')[0].text.replace(",", "")
    #     gd1_4 = data.xpath('//*[@id="Table0"]/tbody/tr[3]/td[4]')[0].text.replace(",", "")
    #     gd1_5 = data.xpath('//*[@id="Table0"]/tbody/tr[3]/td[5]')[0].text.replace(",", "")
    #
    #     # 筹码集中度
    #     gd2_1 = data.xpath('//*[@id="Table0"]/tbody/tr[7]/td[1]')[0].text.replace(",", "")
    #     gd2_2 = data.xpath('//*[@id="Table0"]/tbody/tr[7]/td[2]')[0].text.replace(",", "")
    #     gd2_3 = data.xpath('//*[@id="Table0"]/tbody/tr[7]/td[3]')[0].text.replace(",", "")
    #     gd2_4 = data.xpath('//*[@id="Table0"]/tbody/tr[7]/td[4]')[0].text.replace(",", "")
    #     gd2_5 = data.xpath('//*[@id="Table0"]/tbody/tr[7]/td[5]')[0].text.replace(",", "")
    #
    #     # 前十大股东持股合计(%)
    #     gd3_1 = data.xpath('//*[@id="Table0"]/tbody/tr[10]/td[1]')[0].text.replace(",", "")
    #     gd3_2 = data.xpath('//*[@id="Table0"]/tbody/tr[10]/td[2]')[0].text.replace(",", "")
    #     gd3_3 = data.xpath('//*[@id="Table0"]/tbody/tr[10]/td[3]')[0].text.replace(",", "")
    #     gd3_4 = data.xpath('//*[@id="Table0"]/tbody/tr[10]/td[4]')[0].text.replace(",", "")
    #     gd3_5 = data.xpath('//*[@id="Table0"]/tbody/tr[10]/td[5]')[0].text.replace(",", "")
    #
    # except Exception as e:
    #     print(e)

    str1 += ",'" + str(gd1_1) + "'"
    str1 += ",'" + str(gd1_2) + "'"
    str1 += ",'" + str(gd1_3) + "'"
    str1 += ",'" + str(gd1_4) + "'"
    str1 += ",'" + str(gd1_5) + "'"
    str1 += ",'" + str(gd2_1) + "'"
    str1 += ",'" + str(gd2_2) + "'"
    str1 += ",'" + str(gd2_3) + "'"
    str1 += ",'" + str(gd2_4) + "'"
    str1 += ",'" + str(gd2_5) + "'"
    str1 += ",'" + str(gd3_1) + "'"
    str1 += ",'" + str(gd3_2) + "'"
    str1 += ",'" + str(gd3_3) + "'"
    str1 += ",'" + str(gd3_4) + "'"
    str1 += ",'" + str(gd3_5) + "'"

    # 资产负债表-负债合计 -- ok
    fz = ["0", "0", "0", "0", "0", "0"]
    try:
        i = 0
        index = 0
        while i < 5:
            Url = "http://money.finance.sina.com.cn/corp/go.php/vFD_BalanceSheet/stockid/" + code0 + "/ctrl/" \
                  + str(thisyear - i) + "/displaytype/4.phtml"
            html = getHtml(Url)
            data = etree.HTML(html)

            if index == 0:
                for j in range(20, 200):
                    title = ""
                    try:
                        title = data.xpath('//*[@id="BalanceSheetNewTable0"]/tbody/tr[' + str(j) + ']/td[1]/a')[0].text
                    except Exception as e:
                        print(e)
                    if title == "负债合计":
                        index = j
                        break
            fz[i] = data.xpath('//*[@id="BalanceSheetNewTable0"]/tbody/tr[' + str(index) \
                               + ']/td[2]')[0].text.replace(",", "")
            i += 1

    except Exception as e:
        print(e)

    for i in range(0, len(fz)):
        str1 += ",'" + str(fz[i]) + "'"

    # 现金流量表 -ok
    xj = [["0", "0", "0"], ["0", "0", "0"], ["0", "0", "0"], ["0", "0", "0"], ["0", "0", "0"], ["0", "0", "0"]]

    try:
        i = 0
        index0 = index1 = index2 = 0
        while i < 6:
            Url = "http://money.finance.sina.com.cn/corp/go.php/vFD_CashFlow/stockid/" + code0 + "/ctrl/" \
                  + str(thisyear - i) + "/displaytype/4.phtml"
            html = getHtml(Url)
            data = etree.HTML(html)

            if index0 == 0:
                for j in range(0, 200):
                    title = ""
                    try:
                        title = data.xpath('//*[@id="ProfitStatementNewTable0"]/tbody/tr[' + str(j) + ']/td[1]/a')[
                            0].text
                    except Exception as e:
                        print("xj")
                    if title == "经营活动产生的现金流量净额":
                        index0 = j
                    elif title == "投资活动产生的现金流量净额":
                        index1 = j
                    elif title == "筹资活动产生的现金流量净额":
                        index2 = j

                        # 经营活动产生的现金流量净额
            xj[i][0] = data.xpath('//*[@id="ProfitStatementNewTable0"]/tbody/tr[' + str(index0) + ']/td[2]')[
                0].text.replace(",", "")
            # 投资活动产生的现金流量净额
            xj[i][1] = data.xpath('//*[@id="ProfitStatementNewTable0"]/tbody/tr[' + str(index1) + ']/td[2]')[
                0].text.replace(",", "")
            # 筹资活动产生的现金流量净额
            xj[i][2] = data.xpath('//*[@id="ProfitStatementNewTable0"]/tbody/tr[' + str(index2) + ']/td[2]')[
                0].text.replace(",", "")
            i += 1

    except Exception as e:
        print(e)

    strX = ""
    for i in range(0, 3):
        for j in range(0, len(xj)):
            strX += ",'" + str(xj[j][i]) + "'"

    str1 += strX

    # 总股本、已流通股份 -ok
    gb1 = gb2 = "0"
    try:
        Url = "http://money.finance.sina.com.cn/corp/go.php/vCI_StockStructure/stockid/" + code0 + "/ctrl/" \
              + str(thisyear - i) + "/displaytype/4.phtml"
        html = getHtml(Url)
        data = etree.HTML(html)

        # 总股本(万股)
        gb1 = data.xpath('//*[@id="StockStructureNewTable0"]/tbody/tr[5]/td[2]')[0].text.replace(",", "")
        # 流通A股
        gb2 = data.xpath('//*[@id="StockStructureNewTable0"]/tbody/tr[7]/td[2]')[0].text.replace(",", "")

    except Exception as e:
        print(e)

    str1 += ",'" + str(gb1) + "'"
    str1 += ",'" + str(gb2) + "'"

    # 利润表 营业总成本、销售费用、管理费用、财务费用、研发费用、基本每股收益、营业总收入 - ok
    lr = [["0", "0", "0", "0", "0", "0", "0"], ["0", "0", "0", "0", "0", "0", "0"],
          ["0", "0", "0", "0", "0", "0", "0"], ["0", "0", "0", "0", "0", "0", "0"],
          ["0", "0", "0", "0", "0", "0", "0"], ["0", "0", "0", "0", "0", "0", "0"]]

    try:
        i = 0
        while i < 6:
            Url = "http://money.finance.sina.com.cn/corp/go.php/vFD_ProfitStatement/stockid/" + code0 + "/ctrl/" \
                  + str(thisyear - i) + "/displaytype/4.phtml"
            html = getHtml(Url)
            data = etree.HTML(html)

            index = [0, 0, 0, 0, 0, 0, 0]
            if index[0] == 0:
                for j in range(0, 200):
                    title = ""
                    try:
                        title = data.xpath('//*[@id="ProfitStatementNewTable0"]/tbody/tr[' + str(j) + ']/td[1]/a')[
                            0].text
                    except Exception as e:
                        print("lr")

                    if len(title) < 1:
                        continue

                    if title.find("营业总成本") >= 0 or title.find("营业支出") >= 0:
                        index[0] = j
                    elif title == "销售费用":
                        index[1] = j
                    elif title.find("管理费用") >= 0:
                        index[2] = j
                    elif title == "财务费用":
                        index[3] = j
                    elif title.find("营业收入") >= 0:
                        index[4] = j
                    elif title.find("基本每股收益") >= 0:
                        index[5] = j
                    elif title.find("利润总额") >= 0:
                        index[6] = j

            for j in range(0, len(index)):
                if index[j] > 0:
                    lr[i][j] = data.xpath('//*[@id="ProfitStatementNewTable0"]/tbody/tr[' + str(index[j]) + ']/td[2]')[
                        0].text.replace(",", "")
                else:
                    lr[i][j] = "0"

            i += 1

    except Exception as e:
        print(e)

    strX = ""
    for i in range(0, 7):
        for j in range(0, len(lr)):
            strX += ",'" + str(lr[j][i]) + "'"

    str1 += strX

    # 研发费用
    lr8_1 = lr8_2 = "0"
    # try:
    #     Url = "http://f10.eastmoney.com/NewFinanceAnalysis/Index?type=web&code=" + code + "#lrb-0"
    #     html = getHtml(Url)
    #     data = etree.HTML(html)
    #
    #     # 当年-最新季报
    #     lr8_1 = data.xpath('//*[@id="report_lrb"]/tbody/tr[12]/td[2]/span')[0].text.replace(",", "")
    #     # 研发费用(-1年)
    #     for i in range(0, 3):
    #         date = data.xpath('//*[@id="report_xjllb"]/tbody/tr[1]/th[' + str(3 + i) + ']/span')[0].text.replace(",",
    #                                                                                                              "")
    #         if date[5:6] == "12":
    #             lr8_2 = data.xpath('//*[@id="report_lrb"]/tbody/tr[12]/td[' + str(3 + i) + ']/span')[0].text.replace(
    #                 ",",
    #                 "")
    #             break
    #
    # except Exception as e:
    #     print(e)

    str1 += ",'" + str(lr8_1) + "'"
    str1 += ",'" + str(lr8_2) + "'"

    # 货币资金、应收票据、应收账款 -- ok
    zf = [["0", "0", "0"], ["0", "0", "0"], ["0", "0", "0"], ["0", "0", "0"], ["0", "0", "0"], ["0", "0", "0"]]
    try:
        i = 0
        while i < 5:
            Url = "http://money.finance.sina.com.cn/corp/go.php/vFD_BalanceSheet/stockid/" + code0 + "/ctrl/" \
                  + str(thisyear - i) + "/displaytype/4.phtml"
            html = getHtml(Url)
            data = etree.HTML(html)

            index = [0, 0, 0]
            if index[0] == 0:
                for j in range(0, 200):
                    title = ""
                    try:
                        title = data.xpath('//*[@id="BalanceSheetNewTable0"]/tbody/tr[' + str(j) + ']/td[1]/a')[
                            0].text
                    except Exception as e:
                        print("zf")

                    if len(title) < 1:
                        continue

                    if title.find("货币资金") >= 0:
                        index[0] = j
                    elif title == "应收票据":
                        index[1] = j
                    elif title.find("应收账款") >= 0:
                        index[2] = j

            for j in range(0, len(index)):
                if index[j] > 0:
                    zf[i][j] = data.xpath('//*[@id="BalanceSheetNewTable0"]/tbody/tr[' + str(index[j]) + ']/td[2]')[
                        0].text.replace(",", "")
                else:
                    zf[i][j] = "0"

            i += 1

    except Exception as e:
        print(e)

    strX = ""
    for i in range(0, 3):
        for j in range(0, len(zf)):
            strX += ",'" + str(zf[j][i]) + "'"

    str1 += strX

    # 分红方案 - ok
    fh = [["0", "0", "0", "0"], ["0", "0", "0", "0"], ["0", "0", "0", "0"], ["0", "0", "0", "0"], ["0", "0", "0", "0"]]
    try:
        Url = "http://money.finance.sina.com.cn/corp/go.php/vISSUE_ShareBonus/stockid/" + code0 + ".phtml"
        html = getHtml(Url)
        data = etree.HTML(html)

        # 分红方案(每10股)
        i = 0
        while i < 5:
            # 公告日期
            fh[i][0] = data.xpath('//*[@id="sharebonus_1"]/tbody/tr[' + str(1 + i) + ']/td[1]')[0].text.replace(",", "")
            # 送股(股)
            fh[i][1] = data.xpath('//*[@id="sharebonus_1"]/tbody/tr[' + str(1 + i) + ']/td[2]')[0].text.replace(",", "")
            # 转增(股)
            fh[i][2] = data.xpath('//*[@id="sharebonus_1"]/tbody/tr[' + str(1 + i) + ']/td[3]')[0].text.replace(",", "")
            # 派息(税前)(元)
            fh[i][3] = data.xpath('//*[@id="sharebonus_1"]/tbody/tr[' + str(1 + i) + ']/td[4]')[0].text.replace(",", "")
            i += 1

    except Exception as e:
        print(e)

    strX = ""
    for i in range(0, 4):
        for j in range(0, len(fh)):
            strX += ",'" + str(fh[j][i]) + "'"

    str1 += strX

    try:
        sql += "(" + str1 + ")"
        sql = sql.replace('nan', 'null').replace('None', 'null').replace('none', 'null')
        cursor.execute(sql)
        db.commit()
    except Exception as e:
        print(e)
        print(sql)
        print(code)
        print(a0)
        return False

    return True

# 是否需要加入
def needadd(code):
    global cursor
    global db

    try:
        sql = "SELECT code FROM `fa_stock_dfc` where code = '" + code + "'"
        cursor.execute(sql)
        if result:
            datalist = cursor.fetchall()
            if len(datalist) > 0:
                return 0
    except Exception as e:
        print(e)
        # 如果以
        return 1

    return 1


code_list = []
try:
    sql = "SELECT code FROM `fa_stock_mk` where date = '2019-04-30' "
    result = cursor.execute(sql)

    if result:
        code_list = cursor.fetchall()

except Exception as e:
    print(e)

#### 打印结果集 ####
# sh.600000～sz.300773
num = 0
isFirst = 0

for i in range(0, len(code_list)):
    one = code_list[i]
    if isFirst == 0 and one[0] == "sh.600000":
        isFirst = 1

    if isFirst == 1 and needadd(one[0]) == 1:
        result = querydata(one[0])
        if result == 0:
            continue
        print('----Finish---' + str(num))
        num += 1
        if one[0] == "sz.300773":
            break
        # if num > 0:
        #     break

cursor.close()
db.close()

print('----OK---')
